/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package processor;

import java.awt.Color;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.swing.JOptionPane;
import javax.swing.text.BadLocationException;
import javax.swing.text.DefaultHighlighter;
import javax.swing.text.Highlighter;

/**
 * This class will check grammar of query
 *
 * @author DYNG09
 */
public class SqlGrammar {

    private String query;
    private Highlighter h;
    private int startPos;
    private String allowedCharacter = "=><+-*/%().,'\n\t ";

    public SqlGrammar() {
        this.query = null;
    }

    public SqlGrammar(String query, Highlighter h, int startPos) {
        this.query = query.toLowerCase();
        this.h = h;
        this.startPos = startPos;

        h.removeAllHighlights();
    }

    public boolean check() {
        h.removeAllHighlights();
        //test case 1 : check special character
        if (!this.checkAllowedCharacter()) {
            return false;
        }
        //test case 2 : check start with key word
        if (!this.checkKeyWordAtHead()) {
            return false;
        }
        //test case 3 : check syntax query
//        if(!this.checkSyntax()){
//            return false;
//        }

        return true;
    }

    private void highlightError(int x, int y) {
        try {
            h.addHighlight(x + startPos, y + startPos, new DefaultHighlighter.DefaultHighlightPainter(Color.RED));
        } catch (BadLocationException ex) {
            Logger.getLogger(SqlGrammar.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private boolean checkSyntax() {
        DetectQuery dq = new DetectQuery(this.query.trim());
        while (!"".equals(dq.getQuery())) {
            dq.getASingleQuery();
            CharSequence sq = dq.getSingleQuery();
            Pattern p = null;
            Matcher m;
            String s = null;
            switch (dq.detect()) {
                case Constant.SELECT:
                    p = Pattern.compile(Constant.SELECT1_EXPRESSION);
                    s = "SELECT";
                    break;
                case Constant.SELECT_LV2:
                    p = Pattern.compile(Constant.SELECT2_EXPRESSION);
                    s = "SELECT (LEVEL 2)";
                    break;
                case Constant.INSERT:
                    p = Pattern.compile(Constant.INSERT_EXPRESSION);
                    s = "INSERT";
                    break;
                case Constant.UPDATE:
                    p = Pattern.compile(Constant.UPDATE_EXPRESSION);
                    s = "UPDATE";
                    break;
                case Constant.DELETE:
                    p = Pattern.compile(Constant.DELETE_EXPRESSION);
                    s = "DELETE";
            }
            m = p.matcher(sq);
            if (!m.matches()) {
                JOptionPane.showMessageDialog(null, "No match syntax " + s);
                return false;
            }
        }

        return true;
    }

    /**
     * Check if any single query don't start with keywords : select, insert,
     * update, delete
     *
     * @return
     */
    private boolean checkKeyWordAtHead() {
        String sq = this.query.trim();
        if (!(sq.startsWith("select ")
                || sq.startsWith("insert ")
                || sq.startsWith("update ")
                || sq.startsWith("delete "))) {
            this.highlightError(0, 7);
            JOptionPane.showMessageDialog(null, "Query must be start with: select, insert, update or delete");
            return false;
        }

        return true;
    }

    /**
     * Check if existe the special character is not allowed
     *
     * @return
     */
    private boolean checkAllowedCharacter() {
        for (int i = 0; i < this.query.length(); i++) {
            String c = this.query.substring(i, i + 1);
            if (this.isSpecialCharacter(c) && !this.allowedCharacter.contains(c)) {
                this.highlightError(i, i + 1);
                JOptionPane.showMessageDialog(null, "There is al least a special character is not allowed!");
                return false;
            }
        }
        return true;
    }

    private boolean isSpecialCharacter(String s) {
        char c = s.charAt(0);
        if (!(c >= '0' && c <= '9')) {
            if (!((c >= 'a' && c <= 'z'))) {
                return true;
            }
        }
        return false;
    }

    /**
     * true --> NULL
     *
     * @param query
     * @param s1
     * @param s2
     * @param startPos
     * @return
     */
    private boolean checkNullBetween(String query, String s1, String s2, int startPos) {
        boolean isNull = true;
        int endPos = startPos + 1;
        for (int j = startPos; j < query.length() - s2.length() - 1; j++) {
            if (query.substring(j, j + s2.length()).equals(s2)) {
                endPos = j;
                break;
            }
        }
        String s = query.substring(startPos, endPos);
        for (int j = 0; j < s.length(); j++) {
            if (s.charAt(j) != ' ' && s.charAt(j) != '\t' && s.charAt(j) != '\n') {
                isNull = false;
                break;
            }
        }

        if (isNull) {
            this.highlightError(startPos, endPos);
            JOptionPane.showMessageDialog(null, "There is no expression between "
                    + s1.toUpperCase() + " and " + s2.toUpperCase());
        }

        return isNull;
    }

    /**
     *
     * @param q
     * @param startPos
     * @return
     */
    private boolean checkParentheses(String q, int startPos) {
        for (int i = startPos; i < q.length(); i++) {
            if (q.charAt(i) == '(') {
                for (int j = i + 1; j < q.length(); j++) {
                    if (q.charAt(j) == ')') {
                        return true;
                    }
                }
            }
        }
        JOptionPane.showMessageDialog(null, "An error of parentheses ( )");
        return false;
    }

    private boolean runSelectTestCase(int level) {
        int l = 1;
        for (int i = 0; i < query.length() - 5; i++) {
            //if existe select
            if (query.length() >= 6 && i <= query.length() - 6
                    && query.substring(i, i + 6).equals("select")) {
                //test case 2 - not existe from or one more from
                //int count = countFrom(this.query, i + 6);
                //if (count < 1) {
                //    return false;
                //}
                //test case 3 - there is no expression between SELECT and FROM
                boolean e = checkNullBetween(this.query, "select", "from", i + 6);
                if (e) {
                    return false;
                }
                //test case 4 - if existe where, there is no expression between FROM and WHERE
                int fromPos = i + 7;
                for (int j = fromPos; j < query.length() - 3; j++) {
                    if (query.substring(j, j + 4).equals("from")) {
                        fromPos = j;
                        break;
                    }
                }
                e = checkNullBetween(this.query, "from", "where", fromPos + 4);
                if (e) {
                    return false;
                }

                l++;
                if (l >= level) {
                    break;
                }
            }

        }

        return true;
    }

    private boolean runInsertTestCase(String query) {
        int len = query.length();
        int count = 0;
        for (int i = 0; i < len; i++) {
            //test case 1 - not existe INTO
            if (i < len - 3 && query.substring(i, i + 4).equals("into")) {
                count++;
                for (int j = i + 4; j < len - 5; j++) {
                    if (query.substring(j, j + 6).equals("values")) {
                        //test case : insert into [NULL] values
                        if (checkNullBetween(query, "into", "values", i + 4)) {
                            return false;
                        }
                        //test case : .... values.... - not existe parentheses
                        if (!checkParentheses(query, j + 5)) {
                            return false;
                        }
                    }
                }
                break;
            }
        }
        if (count == 0) {
            JOptionPane.showMessageDialog(null, "Not existe INTO");
            return false;
        }
        return true;
    }

    private boolean runUpdateTestCase(String q) {
        int len = query.length();
        int count = 0;
        for (int i = 0; i < len; i++) {
            //test case 1 - not existe set
            if (i < len - 2 && query.substring(i, i + 3).equals("set")) {
                count++;
                //testcase: update [NULL] set
                if (this.checkNullBetween(q, "update", "set", 6)) {
                    return false;
                }
                for (int j = i + 3; j < len - 4; j++) {
                    if (q.substring(j, j + 5).equals("where")) {
                        //testcase: set [NULL] where
                        if (this.checkNullBetween(q, "set", "where", i + 3)) {
                            return false;
                        }
                        break;
                    }
                }
            }
        }
        if (count == 0) {
            JOptionPane.showMessageDialog(null, "Not existe SET");
            return false;
        }
        return true;
    }

    private boolean runDeleteTestCase(String q) {
        int len = query.length();
        int count = 0;
        for (int i = 0; i < len; i++) {
            //test case 1 - not existe FROM
            if (i < len - 3 && query.substring(i, i + 4).equals("from")) {
                count++;
                for (int j = i + 4; j < len - 4; j++) {
                    if (query.substring(j, j + 5).equals("where")) {
                        //test case : delete from [NULL] where
                        if (checkNullBetween(query, "from", "where", i + 4)) {
                            return false;
                        }
                    }
                }
                break;
            }
        }
        if (count == 0) {
            JOptionPane.showMessageDialog(null, "Not existe FROM");
            return false;
        }
        return true;
    }

    /**
     *
     * @param query
     * @param qName
     * @param startPos
     * @return
     */
    private String subQuery(String query, String qName, int startPos) {
        String q = null;
        for (int i = startPos; i < query.length() - qName.length(); i++) {
            if (query.substring(i, i + qName.length()).equals(qName)) {
                q = query.substring(i, query.length()).trim();
                break;
            }
        }

        return q;
    }

    public boolean checkSelectGrammar(int level) {
        return this.runSelectTestCase(level);
    }

    public boolean checkInsertGrammar() {
        boolean r = true;
        String insertQuery = this.subQuery(this.query, "insert", 0);
        while (insertQuery != null && !"".equals(insertQuery)) {
            r = runInsertTestCase(insertQuery);
            if (r == false) {
                break;
            }
            insertQuery = this.subQuery(insertQuery, "insert", 6);
        }

        return r;
    }

    public boolean checkUpdateGrammar() {
        boolean r = true;
        String updateQuery = this.subQuery(this.query, "update", 0);
        while (updateQuery != null && !"".equals(updateQuery)) {
            r = runUpdateTestCase(updateQuery);
            if (r == false) {
                break;
            }
            updateQuery = this.subQuery(updateQuery, "update", 6);
        }

        return r;
    }

    public boolean checkDeleteGrammar() {
        boolean r = true;
        String deleteQuery = this.subQuery(this.query, "delete", 0);
        while (deleteQuery != null && !"".equals(deleteQuery)) {
            r = runDeleteTestCase(deleteQuery);
            if (r == false) {
                break;
            }
            deleteQuery = this.subQuery(deleteQuery, "delete", 6);
        }

        return r;
    }

    /**
     * @return the query
     */
    public String getQuery() {
        return query;
    }

    /**
     * @param query the query to set
     */
    public void setQuery(String query) {
        this.query = query.toLowerCase();
    }
}
